package database.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import twitter4j.Status;

import database.objects.DatabaseObject;
import database.objects.Message;
import database.objects.MessageOwner;
import database.models.MessageOwnerModel;

/**
 * Class for the MessageModel, based upon the table Message in the database.
 * 
 * @author Project group 9
 * 
 */
public class MessageModel extends Model {

	private MessageOwnerModel messageOwnerModel;
	private static final boolean ONLYTAGS = false;

	/**
	 * Constructor for MessageModel, database connection is taken from the super
	 * Model.
	 * 
	 * @param connection
	 */
	public MessageModel(Connection con, MessageOwnerModel messageOwnerModel) {
		super(con);
		this.messageOwnerModel = messageOwnerModel;
	}

	/**
	 * Get a message by using the id.
	 * 
	 * @param id
	 *            The id of the message
	 * @return Message Object
	 */
	public Message byId(long id) {
		return (Message) load("id", id, null).get(0);
	}

	/**
	 * Get a message based upon a tweetId.
	 * 
	 * @param tweetId
	 *            The id from twitter db
	 * @return Message Object
	 */
	public Message byTweetId(long tweetId) {
		return (Message) load("tweet_id", tweetId, null).get(0);
	}

	/**
	 * Gets the last 10 new tweets from the database.
	 * 
	 * @param keywords
	 *            When these are given, a tweet needs to have this tag.
	 * @return ArrayList with 10 Message objects
	 */
	public ArrayList<Message> getNewMessages(ArrayList<String> keywords) {
		ArrayList<Message> messages = new ArrayList<Message>();
		String or = "";
		boolean first = true;
		if (!keywords.isEmpty()) {
			or += "AND (";
			for (String keyword : keywords) {
				if (first) {
					or += "t.name = '" + keyword + "' ";
					first = false;
				} else {
					or += "OR t.name = '" + keyword + "' ";
				}
			}
			or += ") ";
		}

		String sql = "SELECT m.id, m.text, m.created_at, m.place, m.owner, m.tweet_id, m.is_retweet "
				+ "FROM message m "
				+ "INNER JOIN message_tag mt ON m.id = mt.message "
				+ "INNER JOIN tag t ON mt.tag = t.id "
				+ or
				+ "GROUP BY mt.message "
				+ "HAVING count(*) >= "
				+ keywords.size()
				+ " "
				+ "ORDER BY created_at DESC "
				+ "LIMIT 10 ";
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					messages.add(new Message(rs.getLong("id"), rs
							.getString("text"), rs.getTimestamp("created_at"),
							rs.getString("place"), messageOwnerModel.byId(rs
									.getLong("owner")), rs.getLong("tweet_id"),
							rs.getBoolean("is_retweet")));
				} while (rs.next());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return messages;
	}

	/**
	 * Returns all the tweets from the last minute with the
	 * 
	 * @param keywords
	 * @return
	 */
	public ArrayList<Message> getTwitterStream(ArrayList<String> keywords) {
		Calendar cal = Calendar.getInstance();
		Long newTime = cal.getTimeInMillis() - 60000;
		SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH:");
		String minuteAgo = time.format(newTime);

		ArrayList<Message> messages = new ArrayList<Message>();
		String sql;
		if (ONLYTAGS) {
			sql = "SELECT name, text " + "FROM tag t "
					+ "INNER JOIN message_tag mt ON t.id = mt.tag "
					+ "INNER JOIN message m ON mt.message = m.id "
					+ "INNER JOIN message_owner mo ON m.owner = mo.id "
					+ "WHERE m.created_at > '" + minuteAgo + "' ";
			for (String keyword : keywords) {
				sql += "AND t.name = '" + keyword + "' ";
			}
		} else {
			sql = "SELECT * " + "FROM message m "
					+ "INNER JOIN message_owner mo ON m.owner = mo.id "
					+ "WHERE m.created_at > '" + minuteAgo + "' ";
			for (String keyword : keywords) {
				sql += "AND text LIKE '%" + keyword + "%' ";
			}
		}
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					messages.add(new Message(rs.getLong("m.id"), rs
							.getString("m.text"), rs
							.getTimestamp("m.created_at"), rs
							.getString("m.place"), new MessageOwner(rs
							.getLong("mo.id"), rs.getLong("mo.twitter_id"), rs
							.getString("mo.name"), rs
							.getString("mo.profile_image_url")), rs
							.getLong("m.tweet_id"), rs
							.getBoolean("m.is_retweet")));
				} while (rs.next());
				pst.close();
			} else {
				messages.add(new Message());
				System.out.println("ERROR: no tweets inserted in the last minute.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return messages;
	}

	/**
	 * Load messages from the database, based upon a column, and the key from
	 * that column.
	 * 
	 * @param column
	 *            The column that is searched in.
	 * @param id
	 *            The id that is looked for in the column.
	 * @return DatabaseObject, a Message
	 */
	@Override
	public ArrayList<DatabaseObject> load(String column, long id, String value) {
		ArrayList<DatabaseObject> messages = new ArrayList<DatabaseObject>();
		try {
			PreparedStatement pst = con
					.prepareStatement("SELECT * FROM `message` WHERE `"
							+ column + "` = ?");
			if (id != -1) {
				pst.setLong(1, id);
			} else {
				pst.setString(1, value);
			}
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					messages.add(new Message(rs.getLong("id"), rs
							.getString("text"), rs.getTimestamp("created_at"),
							rs.getString("place"),
							messageOwnerModel.byId(rs
									.getLong("owner")), rs.getLong("tweet_id"),
							rs.getBoolean("is_reTweet")));
				} while (rs.next());
			} else {
				messages.add(new Message());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return messages;
	}

	/**
	 * Saves a message into the database
	 * 
	 * @param dbo
	 *            A DatabaseObject, in this case a Message.
	 */
	public void save(Status status) {
		String place = "";
		if (status.getPlace() != null) {
			place = status.getPlace().getName();
		}
		try {
			PreparedStatement pst = con
					.prepareStatement("INSERT INTO `message` (`text`, `place`, `owner`, `tweet_id`, `is_retweet`) VALUES (?,?,?,?,?)");
			pst.setString(1, status.getText());
			//this is the time of the tweet, we now take the time it is inserted in the database. 
			//pst.setTimestamp(2, new java.sql.Timestamp(status.getCreatedAt()
					//.getTime()));
			pst.setString(2, place);
			pst.setLong(3,
					messageOwnerModel.byTwitterId(status.getUser().getId())
							.getId());
			pst.setLong(4, status.getId());
			pst.setBoolean(5, status.isRetweet());
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
